const uuid = require('uuid')

// 创建数据库或者打开
function openSqlite() {
	plus.sqlite.openDatabase({
		name: 'cpis', //数据库名称
		path: '_doc/cpis.db', //数据库地址，uniapp推荐以下划线为开头，这到底存在哪里去了，我也不清楚
		success(e) {
			console.log("sqlite数据库打开成功");
		},
		fail(e) {
			console.log("sqlite数据库打开失败:", e);
		}
	})
}

// 创建数据字典表
function createMCpisCatDic() {
	plus.sqlite.executeSql({
		name: 'cpis',
		sql: 'create table if not exists m_cpis_cat_dic("id" TEXT PRIMARY KEY,"type_id" TEXT,"type_key" TEXT,"key" TEXT,"name" TEXT,"parent_id" TEXT)',
		success(e) {
			console.log("创建表【m_cpis_cat_dic】成功")
		},
		fail(e) {
			console.log("创建表【m_cpis_cat_dic】失败:", e)
		}
	})
}

// 创建附件文件表
function createMCpisFileAttachment() {
	plus.sqlite.executeSql({
		name: 'cpis',
		sql: 'create table if not exists m_cpis_file_attachment("id" TEXT PRIMARY KEY,"file_name" TEXT,"file_path" TEXT,"ext" TEXT,"upload_state" TEXT,"attachment_id" TEXT,"upload_time" TEXT)',
		success(e) {
			console.log("创建表【m_cpis_file_attachment】成功")
		},
		fail(e) {
			console.log("创建表【m_cpis_file_attachment】失败:", e)
		}
	})
}

// 向数据字典表里添加数据
function insertMCpisCatDic(obj) {
	//判断有没有传参
	if (obj !== undefined) {
		//判断传的参是否有值
		var b = (JSON.stringify(obj) == "{}");
		if (!b) {
			var id = obj.id;
			var typeId = obj.typeId;
			var typeKey = obj.typeKey;
			var key = obj.key;
			var name = obj.name;
			var parentId = obj.parentId;
			
			plus.sqlite.executeSql({
				name: 'cpis',
				sql: 'insert into m_cpis_cat_dic(id,type_id,type_key,key,name,parent_id) values("' + 
					id + '","' + typeId + '","' + typeKey + '","' + key + '","' + name + '","' + parentId + '")',
				success(e) {
					console.log("表【m_cpis_cat_dic】数据插入成功");
				},
				fail(e) {
					console.log("表【m_cpis_cat_dic】数据插入失败:", e);
				}
			})
		} else {
			console.log("表【m_cpis_cat_dic】数据插入失败:参数为空");
		}
	} else {
		console.log("表【m_cpis_cat_dic】数据插入失败:参数为空");
	}
}

// 批量向数据字典表里添加数据
function insertMCpisCatDicBatch(arr) {
	let sqls = []
	arr.forEach(obj => {
		//判断有没有传参
		if (obj !== undefined) {
			//判断传的参是否有值
			var b = (JSON.stringify(obj) == "{}");
			if (!b) {
				var id = obj.id;
				var typeId = obj.typeId;
				var typeKey = obj.typeKey;
				var key = obj.key;
				var name = obj.name;
				var parentId = obj.parentId;
				
				let sql = 'insert into m_cpis_cat_dic(id,type_id,type_key,key,name,parent_id) values("' + 
					id + '","' + typeId + '","' + typeKey + '","' + key + '","' + name + '","' + parentId + '")';
				
				sqls.push(sql)
			} else {
				console.log("表【m_cpis_cat_dic】数据插入失败:参数为空");
			}
		} else {
			console.log("表【m_cpis_cat_dic】数据插入失败:参数为空");
		}
	})
	
	if(sqls.length > 0) {
		plus.sqlite.executeSql({
			name: 'cpis',
			sql: sqls,
			success(e) {
				console.log("表【m_cpis_cat_dic】数据插入成功");
			},
			fail(e) {
				console.log("表【m_cpis_cat_dic】数据插入失败:", e);
			}
		})
	}
}

// 向附件文件表里添加数据
function insertMCpisFileAttachment(obj) {
	//判断有没有传参
	if (obj !== undefined) {
		//判断传的参是否有值
		var b = (JSON.stringify(obj) == "{}");
		if (!b) {
			var id = uuid();
			var fileName = obj.fileName;
			var filePath = obj.filePath;
			var ext = obj.ext;
			var uploadState = obj.uploadState;
			var attachmentId = obj.attachmentId;
			var uploadTime = obj.uploadTime;
			
			return new Promise((resolve, reject) => {
				plus.sqlite.executeSql({
					name: 'cpis',
					sql: 'insert into m_cpis_file_attachment(id,file_name,file_path,ext,upload_state,attachment_id,upload_time) values("' + 
						id + '","' + fileName + '","' + filePath + '","' + ext + '","' + uploadState + '","' + attachmentId + '","' + uploadTime + '")',
					success(e) {
						resolve(id);
						console.log("表【m_cpis_file_attachment】数据插入成功");
					},
					fail(e) {
						reject(e);
						console.log("表【m_cpis_file_attachment】数据插入失败:", e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("表【m_cpis_file_attachment】数据插入失败:参数为空")
			});
		}
	} else {
		return new Promise((resolve, reject) => {
			reject("表【m_cpis_file_attachment】数据插入失败:参数为空")
		});
	}
}

// 一次获取指定数据条数
function pullSQL(tblName, num) {
	plus.sqlite.selectSql({
		name: 'cpis',
		sql: "select * from " + tblName + " order by list desc limit 15 offset " + num,
		success(e) {
			console.log("查询成功");
		},
		fail(e) {
			console.log(e);
		}
	})
}

// 查询表数据
function selectTableInfo(tblName, aa, bb, cc, dd) {
	if (tblName !== undefined) {
		//第一个是表名，后两个参数是列名，用来检索
		if (aa !== undefined && cc !== undefined) {
			//两个检索条件
			var sql = "select * from " + tblName + " where " + aa + " = '" + bb + "' and " + cc + " = '" + dd + "'";
		}
		if (aa !== undefined && cc == undefined) {
			//一个检索条件
			var sql = "select * from " + tblName + " where " + aa + " = '" + bb + "'";
		}
		if (aa == undefined) {
			var sql = "select * from " + tblName;
		}
		console.log("sql语句：" + sql)
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: 'cpis',
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误查询")
		});
	}
}

// 查询表数据--不限条件个数
function selectTableInfoMore(tblName, condition) {
	if (tblName !== undefined) {
		var sql = "select * from " + tblName
		if (condition !== undefined) {
			sql += " where "
			let length = condition.length
			for(let i=0; i<length; i++) {
				if(i === length-1) {
					sql += condition[i].key + " = '" + condition[i].value + "'"
				} else {
					sql += condition[i].key + " = '" + condition[i].value + "' and "
				}
			}
		}
		console.log("sql语句：" + sql)
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: 'cpis',
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误查询")
		});
	}
}

// 删除表数据
function deleteTableInfo(tblName, sol, qq, ww, ee) {
	if (tblName !== undefined) {
		//第一个是表名,后面两个是列名，检索用的
		if (sol !== undefined && ww !== undefined) {
			//两个检索条件
			var sql = "delete from " + tblName + " where " + sol + " = '" + qq + "' and " + ww + " = '" + ee + "'";
		} 
		if (sol !== undefined && ww == undefined) {
			//一个检索条件
			var sql = "delete from " + tblName + " where " + sol + " = '" + qq + "'";
		}
		if (sol == undefined) {
			var sql = "delete from " + tblName;
		}
		console.log("sql语句：" + sql)
		plus.sqlite.executeSql({
			name: 'cpis',
			sql: sql,
			success(e) {
				console.log("删除成功");
			},
			fail(e) {
				console.log(e);
			}
		})
	} else {
		console.log("错误删除");
	}
}

// 更新表数据
function modifyTableInfo(tblName, data, condition) {
	var sql = "update " + tblName + " set ";
	// 更新数据
	if(data !== undefined) {
		let length = data.length
		for(let i=0; i<length; i++) {
			if(i === length-1) {
				sql += data[i].key + " = '" + data[i].value + "'"
			} else {
				sql += data[i].key + " = '" + data[i].value + "' , "
			}
		}
	}
	// 更新条件
	if (condition !== undefined) {
		sql += " where "
		let length = condition.length
		for(let i=0; i<length; i++) {
			if(i === length-1) {
				sql += condition[i].key + " = '" + condition[i].value + "'"
			} else {
				sql += condition[i].key + " = '" + condition[i].value + "' and "
			}
		}
	}
	console.log("sql语句：" + sql)
	plus.sqlite.executeSql({
		name: 'cpis',
		sql: sql,
		success(e) {
			console.log("修改成功");
		},
		fail(e) {
			console.log(e);
		}
	})
}

// 批量更新表数据
function modifyTableInfoBatch(tblName, arr, condition) {
	let sqls = []
	arr.forEach(item => {
		let data = []
		for(const [key, value] of  Object.entries(item)) {
			data.push({
				key: key,
				value: value
			})
		}
		// 删除第一个元素（主键），防止更新报错
		data.shift()
		
		var sql = "update " + tblName + " set ";
		// 更新数据
		if(data !== undefined) {
			let length = data.length
			for(let i=0; i<length; i++) {
				if(i === length-1) {
					sql += data[i].key + " = '" + data[i].value + "'"
				} else {
					sql += data[i].key + " = '" + data[i].value + "' , "
				}
			}
		}
		
		// 更新条件
		if (condition !== undefined) {
			sql += " where "
			let length = condition.length
			for(let i=0; i<length; i++) {
				if(i === length-1) {
					sql += condition[i] + " = '" + item[condition[i]] + "'"
				} else {
					sql += condition[i] + " = '" + item[condition[i]] + "' and "
				}
			}
		}
		
		sqls.push(sql)
	})

	if(sqls.length > 0) {
		plus.sqlite.executeSql({
			name: 'cpis',
			sql: sqls,
			success(e) {
				console.log("修改成功"); 
			},
			fail(e) {
				console.log(e);
			}
		})
	}
}

// 删除表数据
function dropTable(tblName) {
	if (tblName !== undefined) {
		var sql = "drop table " + tblName;
		console.log("sql语句：" + sql)
		plus.sqlite.executeSql({
			name: 'cpis',
			sql: sql,
			success(e) {
				console.log("删除成功");
			},
			fail(e) {
				console.log(e);
			}
		})
	} else {
		console.log("错误删除");
	}
}

// 关闭数据库
function closeSQL(name) {
	plus.sqlite.closeDatabase({
		name: 'cpis',
		success(e) {
			console.log("关闭成功");
		},
		fail(e) {
			console.log(e);
		}
	})
}

// 监听数据库是否开启
function isOpen(name, path) {
	var ss = name || 'cpis';
	var qq = path || '_doc/cpis.db';
	//数据库打开了就返回true,否则返回false
	var open = plus.sqlite.isOpenDatabase({
		name: ss,
		path: qq
	})
	return open;
}

//把这些方法导出去
export {
	openSqlite,
	createMCpisFileAttachment,
	createMCpisCatDic,
	insertMCpisFileAttachment,
	insertMCpisCatDic,
	insertMCpisCatDicBatch,
	pullSQL,
	selectTableInfo,
	selectTableInfoMore,
	deleteTableInfo,
	modifyTableInfo,
	modifyTableInfoBatch,
	isOpen,
	closeSQL,
	dropTable
}
